/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package library;

import frame.Configurate;
import java.io.DataInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import kbcquiz.App;

public class DB {
    private static String connectionString = "";
    private static Connection connection;

    public static String getConnectionString() {
        if (connectionString.equals("")) {
            String host = null;
            String db = null;
            String user = null;
            String pass = null;
            
            FileInputStream fileInputStream = null;
            try {
            File fileName = new File("C:\\Users\\Thien\\Desktop\\hvaptechkbcquiz\\src\\kbcquiz\\Config.properties");
            fileInputStream = new FileInputStream(fileName);
            DataInputStream  dataInputStream = new DataInputStream(fileInputStream);
            
            host = dataInputStream.readUTF();
            db = dataInputStream.readUTF();
            user = dataInputStream.readUTF();
            pass = dataInputStream.readUTF();
            
        } catch (IOException ex) {
            Logger.getLogger(Configurate.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                fileInputStream.close();
            } catch (IOException ex) {
                Logger.getLogger(Configurate.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
            
            
            //jdbc:sqlserver://localhost;database=AdventureWorks;integratedSecurity=true;
            connectionString = "jdbc:sqlserver://" + host + ":1433;database=" + db + ";user=" + user + ";password=" + pass + ";";

        }
        return connectionString;
    }

    public static Connection getConnection() throws SQLException {
        try {
            if (connection == null || connection.isClosed()) {
                try {
                    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
                    connection = java.sql.DriverManager.getConnection(getConnectionString(), App.getConfig().get("db_username"), App.getConfig().get("db_password"));
                }
                catch (ClassNotFoundException ex) {
                    Utils.Alert("Could not find any driver to connect to Db\n" + ex.getMessage());
                }
            }
        }
        catch (SQLException ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
            Utils.Alert(ex.getMessage() + "\n" + ex.getSQLState());
        }
        return connection;
    }

    public static Boolean exec(String query) {
        Boolean r = false;
        try {
            r = getConnection().createStatement().execute(query);
        }
        catch (SQLException ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
            Utils.Alert(ex.getMessage());
        }
        return r;
    }

    public static ResultSet execQuery(String query) {
        ResultSet rs = null;
        try {
            rs = getConnection().createStatement().executeQuery(query);
        }
        catch (SQLException ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
            Utils.Alert(ex.getMessage() + "\n" + ex.getSQLState());
        }
        return rs;
    }

    public static int execUpdate(String query) {
        int r = 0;
        try {
            r = getConnection().createStatement().executeUpdate(query);
        }
        catch (SQLException ex) {
            Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
            Utils.Alert(ex.getMessage() + "\n" + ex.getSQLState());
        }
        return r;
    }

    public static void close(){
        try{
            connection.close();
        }
        catch(Exception ex){
            
        }
    }
}
